package com.dstz.form.manager.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.dstz.base.api.exception.BusinessMessage;
import com.dstz.base.api.query.QueryFilter;
import com.dstz.base.api.query.QueryOP;
import com.dstz.base.api.response.impl.ResultMsg;
import com.dstz.base.core.id.IdUtil;
import com.dstz.base.core.util.ExceptionUtil;
import com.dstz.base.core.util.StringUtil;
import com.dstz.base.db.model.query.DefaultQueryFilter;
import com.dstz.base.manager.impl.BaseManager;
import com.dstz.form.constants.ViewConstants.BtnClickTypeConstants;
import com.dstz.form.dao.FormCustSqlDao;
import com.dstz.form.manager.FormCustSqlFieldsManager;
import com.dstz.form.manager.FormCustSqlManager;
import com.dstz.form.manager.FormCustSqlRelationManager;
import com.dstz.form.model.custsql.FormCustSql;
import com.dstz.form.model.custsql.FormCustSqlFields;
import com.dstz.form.model.custsql.FormCustSqlRelation;
import com.dstz.form.util.JSONObjectUtil;
import com.dstz.form.util.JdbcTemplateUtil;
import com.dstz.form.util.SqlUtil;
import com.dstz.form.util.TableOperUtil;
import com.dstz.form.util.Util;
import com.dstz.form.vo.SqlButtonVO;
import com.dstz.form.vo.SqlConditionVO;
import com.dstz.form.vo.SqlFieldsVO;
import com.dstz.sys.api.freemark.IFreemarkerEngine;
import com.dstz.sys.api.service.ISysDataSourceService;
import com.dstz.sys.util.ContextUtil;

import net.sf.jsqlparser.JSQLParserException;

/**
 * @description:自定义列表 的ManagerImpl
 * @company:宇宙大魔王
 * @author:xianggang
 * @date:2019-08-31 12:21:58
 */
@Service("formCustSqlManager")
public class FormCustSqlManagerImpl extends BaseManager<String, FormCustSql> implements FormCustSqlManager {
	
	protected Logger logger = LoggerFactory.getLogger(this.getClass());
	
	@Resource
	FormCustSqlDao formCustSqlDao;
	
	@Resource
	FormCustSqlFieldsManager formCustSqlFieldsManager;
	
	@Autowired
	IFreemarkerEngine freemarkerEngine;
	
	@Autowired
	ISysDataSourceService sysDataSourceService;
	@Autowired
	FormCustSqlRelationManager formCustSqlRelationMananger;
	
	@Override
	public ResultMsg<String> save(FormCustSql formCustSql) {
		
		String id = formCustSql.getId();
		
		ResultMsg<String> checkParams = checkParams(formCustSql, id);
		if (!checkParams.getIsOk()) {
			return checkParams;
		}
		
		formCustSql.setButtons(JSONObjectUtil.toListString(formCustSql.getButtons(), SqlButtonVO.class));
		
		formCustSql.setConditions(JSONObjectUtil.toListString(formCustSql.getConditions(), SqlConditionVO.class));
		
		if (formCustSql.getCustForm() != null) {
			formCustSql.setFormKey(formCustSql.getCustForm().getFormKey());
		}
		
		// 新增
		if (StringUtil.isEmpty(id)) {
			// 保存主表
			id = IdUtil.getSuid();
			formCustSql.setCreateBy(ContextUtil.getCurrentUserId());
			formCustSql.setCreator(ContextUtil.getCurrentUserName());
			formCustSql.setCreateTime(new Date());
			formCustSql.setId(id);
			formCustSql.setVersion(1);
			create(formCustSql);
			
			// 保存字段列表
			saveSqlFIelds(formCustSql, id);
			
			saveCustSqlFormRelation(formCustSql);
			
			return ResultMsg.SUCCESS(id);
		}
		
		// 修改
		formCustSql.setUpdateBy(ContextUtil.getCurrentUserId());
		formCustSql.setUpdator(ContextUtil.getCurrentUserName());
		formCustSql.setUpdateTime(new Date());
		formCustSql.setVersion(formCustSql.getVersion() + 1);
		update(formCustSql);
		
		// 查询字段列表
		List<FormCustSqlFields> oldFieldsList = formCustSqlFieldsManager.getListBySqlId(id);
		
		List<String> idList = oldFieldsList.stream().map(FormCustSqlFields::getId).collect(Collectors.toList());
		// 删掉原来的字段配置
		formCustSqlFieldsManager.removeByIdList(idList);
		
		// 保存新的字段列表
		saveSqlFIelds(formCustSql, id);
		
		// 保存表单关系
		saveCustSqlFormRelation(formCustSql);
		
		return ResultMsg.SUCCESS(id);
	}
	
	private void saveCustSqlFormRelation(FormCustSql formCustSql) {
		if (StringUtil.isEmpty(formCustSql.getFormKey()))
			return;
		
		FormCustSqlRelation formCustSqlRelation = formCustSql.getCustForm();
		
		FormCustSqlRelation existsRelation = formCustSqlRelationMananger.getByFormKey(formCustSql.getFormKey());
		if (existsRelation != null && existsRelation.getListId().equals(formCustSqlRelation.getId())) {
			FormCustSql formSql = this.get(existsRelation.getId());
			String formSqlName = formSql == null ? "未知???" : formSql.getName();
			throw new BusinessMessage("表单[" + formCustSqlRelation.getFormName() + "]已经被自定义列表[" + formSqlName
			        + "] 使用，请重新创建一个新的表单，为当前列表专用！");
		}
		
		formCustSqlRelation.setListId(formCustSql.getId());
		
		if (StringUtil.isEmpty(formCustSqlRelation.getId())) {
			formCustSqlRelationMananger.create(formCustSqlRelation);
		} else {
			formCustSqlRelationMananger.update(formCustSqlRelation);
		}
		
	}
	
	// 检测参数
	private ResultMsg<String> checkParams(FormCustSql formCustSql, String id) {
		// 检查别名是否存在
		QueryFilter queryFilter = new DefaultQueryFilter();
		if (StringUtil.isNotEmpty(id)) {
			queryFilter.addFilter("id_", id, QueryOP.NOT_EQUAL);
		}
		
		String code = formCustSql.getCode();
		queryFilter.addFilter("code_", code, QueryOP.EQUAL);
		List<FormCustSql> query = formCustSqlDao.query(queryFilter);
		if (!query.isEmpty()) {
			return ResultMsg.ERROR("编码已存在,code:" + code);
		}
		
		// 主表
		String tableName = formCustSql.getTableName();
		if (StringUtil.isEmpty(tableName)) {
			logger.error("自定义SQL的主表不存在,code:{}", code);
			return ResultMsg.ERROR("自定义SQL的主表不存在");
		}
		
		List<FormCustSqlFields> newFieldsList = formCustSql.getFieldsList();
		Map<String, String> fieldNameColumnMap = newFieldsList.stream()
		        .filter(fields -> Util.equals(fields.getTableName(), tableName))// 过滤主表的字段
		        .collect(Collectors.toMap(FormCustSqlFields::getFieldName, FormCustSqlFields::getName));
		
		// 主键
		String pkName = formCustSql.getPkName();
		if (StringUtil.isEmpty(fieldNameColumnMap.get(pkName))) {
			logger.error("自定义SQL的主键不存在,code:{},tableName:{},pkName:{}", code, tableName, pkName);
			return ResultMsg.ERROR("自定义SQL的主键不存在,tableName:" + tableName + ",pkName:" + pkName);
		}
		
		List<String> columnList = newFieldsList.stream().map(FormCustSqlFields::getName).collect(Collectors.toList());
		
		// 检查按钮配置是否正常
		List<SqlButtonVO> buttonList = JSONObjectUtil.toList(formCustSql.getButtons(), SqlButtonVO.class);
		
		if (CollectionUtils.isEmpty(buttonList)) {
			return ResultMsg.SUCCESS();
		}
		// 根据按钮别名去重
		ArrayList<SqlButtonVO> uniqueAliasButtonList = buttonList.stream()
		        .collect(Collectors.collectingAndThen(
		                Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(SqlButtonVO::getAlias))),
		                ArrayList::new));
		
		if (buttonList.size() != uniqueAliasButtonList.size()) {
			logger.error("请检查按钮别名是否重复,code:{},tableName:{},pkName:{}", code, tableName, pkName);
			return ResultMsg.ERROR("请检查自定义按钮别名是否重复");
		}
		
		if (!CollectionUtils.isEmpty(buttonList)) {
			for (SqlButtonVO sqlButtonVO : buttonList) {
				if (Util.equals(sqlButtonVO.getClickType(), BtnClickTypeConstants.BTNCLICKTYPE_EVENT)) {
					String dataChangeStr = sqlButtonVO.getDataChange();
					
					String[] dataChangeArray = dataChangeStr.split(";");
					
					for (String dataChange : dataChangeArray) {
						String[] changeData = dataChange.split("=");
						String buttonAlias = sqlButtonVO.getAlias();
						if (changeData.length != 2) {
							logger.error("自定义按钮数值变化配置不正确,code:{},btnAlias:{},dataChange:{}", code, buttonAlias,
							        dataChange);
							return ResultMsg.ERROR("自定义按钮数值变化配置不正确:" + dataChange);
						}
						String fieldName = changeData[0];
						String value = changeData[1];
						// 先判断columnList中是否有该字段
						if (!columnList.contains(fieldName) && !fieldNameColumnMap.containsKey(fieldName)) {
							logger.error("自定义按钮数值变化配置不正确,主表中找不到对应的字段,code:{},btnAlias:{},dataChange:{},column:{}", code,
							        buttonAlias, dataChange, fieldName);
							return ResultMsg.ERROR("自定义按钮数值变化配置不正确,主表中找不到对应的字段:" + dataChange);
						}
						if (StringUtil.isEmpty(value)) {
							return ResultMsg.ERROR("自定义按钮数值变化配置不正确,数据不能为空:" + dataChange);
						}
					}
					
				}
			}
		}
		
		return ResultMsg.SUCCESS();
	}
	
	private void saveSqlFIelds(FormCustSql formCustSql, String id) {
		// 保存子表
		List<FormCustSqlFields> fieldsList = formCustSql.getFieldsList();
		for (FormCustSqlFields formCustSqlFields : fieldsList) {
			formCustSqlFields.setId(IdUtil.getSuid());
			formCustSqlFields.setCreateBy(ContextUtil.getCurrentUserId());
			formCustSqlFields.setCreator(ContextUtil.getCurrentUserName());
			formCustSqlFields.setCreateTime(new Date());
			formCustSqlFields.setSqlId(id);
			formCustSqlFieldsManager.create(formCustSqlFields);
		}
	}
	
	@Override
	public ResultMsg<List<SqlFieldsVO>> parseSql(FormCustSql formCustSql) {
		
		String sqlId = formCustSql.getId();
		String sql = formCustSql.getSqlContent();
		String dsKey = formCustSql.getDsKey();
		
		Map<String, Object> reJson = new HashMap<>();
		sql = freemarkerEngine.parseByString(sql, reJson);
		sql = getSql(sql);
		sql = SqlUtil.removeDataSql(sql);
		
		ResultMsg<String> executeSql = JdbcTemplateUtil.executeSql(dsKey, sql);
		if (!executeSql.getIsOk()) {
			logger.error("解析sql异常:{}", executeSql.getMsg());
			return ResultMsg.ERROR(executeSql.getMsg());
		}
		
		ResultMsg<List<SqlFieldsVO>> rsp = getFieldListBySql(dsKey, sql);
		if (!rsp.getIsOk()) {
			return ResultMsg.ERROR(rsp.getMsg());
		}
		
		List<SqlFieldsVO> fields = rsp.getData();
		
		// 如果是更新,则需要保留原始的配置
		if (StringUtil.isNotEmpty(sqlId)) {
			// 查询字段列表
			List<FormCustSqlFields> fieldsList = formCustSqlFieldsManager.getListBySqlId(sqlId);
			for (SqlFieldsVO field : fields) {
				for (FormCustSqlFields oldFields : fieldsList) {
					if (oldFields.getFieldName().equals(field.getFieldName())) {
						
						// 不能全部copy
						// BeanCopierUtils.copyProperties(oldFields, field);
						
						if (StringUtil.isNotEmpty(oldFields.getFieldDesc())) {
							field.setFieldDesc(oldFields.getFieldDesc());
						}
						if (StringUtil.isNotEmpty(oldFields.getAlign())) {
							field.setAlign(oldFields.getAlign());
						}
						if (StringUtil.isNotEmpty(oldFields.getHidden())) {
							field.setHidden(oldFields.getHidden());
						}
						if (StringUtil.isNotEmpty(oldFields.getUnit())) {
							field.setUnit(oldFields.getUnit());
						}
						if (StringUtil.isNotEmpty(oldFields.getWidth())) {
							field.setWidth(oldFields.getWidth());
						}
						if (StringUtil.isNotEmpty(oldFields.getUnit())) {
							field.setUnit(oldFields.getUnit());
						}
						if (StringUtil.isNotEmpty(oldFields.getSn())) {
							field.setSn(oldFields.getSn());
						}
						if (StringUtil.isNotEmpty(oldFields.getHref())) {
							field.setHref(oldFields.getHref());
						}
						if (StringUtil.isNotEmpty(oldFields.getControlType())) {
							field.setControlType(oldFields.getControlType());
						}
						if (StringUtil.isNotEmpty(oldFields.getDateFormat())) {
							field.setDateFormat(oldFields.getDateFormat());
						}
						if (StringUtil.isNotEmpty(oldFields.getExpand())) {
							field.setExpand(oldFields.getExpand());
						}
						if (StringUtil.isNotEmpty(oldFields.getAddAble())) {
							field.setAddAble(oldFields.getAddAble());
						}
						if (StringUtil.isNotEmpty(oldFields.getEditAble())) {
							field.setEditAble(oldFields.getEditAble());
						}
						if (StringUtil.isNotEmpty(oldFields.getImportAble())) {
							field.setImportAble(oldFields.getImportAble());
						}
						if (StringUtil.isNotEmpty(oldFields.getExportAble())) {
							field.setExportAble(oldFields.getExportAble());
						}
						if (StringUtil.isNotEmpty(oldFields.getRules())) {
							field.setRules(oldFields.getRules());
						}
						if (StringUtil.isNotEmpty(oldFields.getPlaceholder())) {
							field.setPlaceholder(oldFields.getPlaceholder());
						}
						if (StringUtil.isNotEmpty(oldFields.getFuzz())) {
							field.setFuzz(oldFields.getFuzz());
						}
						
						break;
					}
				}
			}
			
		}
		// ISysDataSource sysDataSource = sysDataSourceService.getByKey(dsKey);
		// JdbcTemplate jdbcTemplate = sysDataSourceService.getJdbcTemplateByKey(dsKey);
		// Map<String, String> map = new HashMap<>();// Map<表/视图名,表/视图描述>
		// DbOperator dbOperator = DbOperatorFactory.newOperator(sysDataSource.getDbType(), jdbcTemplate);
		
		return rsp;
	}
	
	// 根据Sql获取字段列表
	private ResultMsg<List<SqlFieldsVO>> getFieldListBySql(String dsKey, String sql) {
		TableOperUtil tableOperUtil = new TableOperUtil();
		ResultMsg<List<SqlFieldsVO>> fieldListRsp = tableOperUtil.getColumnModalBySql(sql, dsKey);
		if (!fieldListRsp.getIsOk()) {
			return ResultMsg.ERROR(fieldListRsp.getMsg());
		}
		
		List<SqlFieldsVO> fieldList = fieldListRsp.getData();
		
		JSONObject columnTableRef = null;
		try {
			columnTableRef = SqlUtil.getColumnTableRefJo(sql);
		} catch (JSQLParserException e) {
			return ResultMsg.ERROR(ExceptionUtil.getExceptionMessage(e));
		}
		
		JSONObject defaultTable = columnTableRef.getJSONObject("default");
		
		String tableAlias = "";
		if (defaultTable != null) {
			tableAlias = defaultTable.getString("tableAlias");
		}
		
		// 虽然之前已经获取到了表名 但是由于可能出现一个sql中多个相同表关联查询的情况,所以还需要根据表的别名来区分不同字段对应于哪个表
		for (SqlFieldsVO sqlFieldVO : fieldList) {
			String column = sqlFieldVO.getFieldName().toLowerCase() + "." + sqlFieldVO.getName().toLowerCase();
			if (!columnTableRef.containsKey(column) && StringUtil.isEmpty(sqlFieldVO.getTableAlias())) {
				sqlFieldVO.setTableAlias(tableAlias);
				continue;
			}
			
			JSONObject jo = (JSONObject) columnTableRef.get(column);
			
			sqlFieldVO.setTableAlias(jo.getString("tableAlias"));
			if (jo.containsKey("tableName")) {
				sqlFieldVO.setTableName(jo.getString("tableName"));
			}
		}
		return ResultMsg.SUCCESS(fieldList);
	}
	
	/**
	* 【转换${}规则为万能SQL，方便SQL执行解析】
	* 
	* @param sql
	* @return
	*/
	private String getSql(String sql) {
		String regex = "\\$\\{\\w+\\}";
		Pattern p = Pattern.compile(regex);
		Matcher m = p.matcher(sql);
		while (m.find()) {
			String whereParam = m.group();
			// System.out.println(whereParam);
			sql = sql.replace(whereParam, "'' or 1=1 or 1=''");
			sql = sql.replace("'''", "''");
			// System.out.println(sql);
		}
		// 兼容图表
		regex = "\\{\\w+\\}";
		p = Pattern.compile(regex);
		m = p.matcher(sql);
		while (m.find()) {
			String whereParam = m.group();
			// System.out.println(whereParam);
			sql = sql.replace(whereParam, " 1=1 ");
			// System.out.println(sql);
		}
		return sql;
	}
	
	/**
	 * 根据别名获取自定义sql对象
	 * @param code
	 * @return
	 * @return FormCustSql
	 * @author xianggang
	 * @date 2019年9月2日 上午9:59:36
	 */
	@Override
	public FormCustSql getByCode(String code, boolean getFields) {
		QueryFilter queryFilter = new DefaultQueryFilter();
		queryFilter.addFilter("code_", code, QueryOP.EQUAL);
		List<FormCustSql> query = formCustSqlDao.query(queryFilter);
		if (query.isEmpty()) {
			return null;
		}
		FormCustSql sql = query.get(0);
		
		if (getFields) {
			List<FormCustSqlFields> fieldsList = formCustSqlFieldsManager.getListBySqlId(sql.getId());
			sql.setFieldsList(fieldsList);
		}
		
		return sql;
	}
	
	/**
	 * 导出自定义sql
	 * @param id
	 * @return
	 * @author xianggang
	 * @date 2019年9月4日 下午12:07:34
	 */
	@Override
	public ResultMsg<JSONObject> export(String id) {
		
		List<String> idList = Arrays.asList(id.split(","));
		
		QueryFilter queryFilter = new DefaultQueryFilter();
		queryFilter.addFilter("id_", idList, QueryOP.IN);
		List<FormCustSql> sqlList = formCustSqlDao.query(queryFilter);
		
		// 查询字段列表
		for (FormCustSql formCustSql : sqlList) {
			
			// 字段配置
			List<FormCustSqlFields> fieldsList = formCustSqlFieldsManager.getListBySqlId(formCustSql.getId());
			formCustSql.setFieldsList(fieldsList);
			
			// 在线表单
			FormCustSqlRelation custForm = formCustSqlRelationMananger.getByFormKey(formCustSql.getFormKey());
			formCustSql.setCustForm(custForm);
		}
		
		JSONObject exportData = new JSONObject();
		exportData.put("sqls", JSON.toJSONString(sqlList));
		exportData.put("fileName", sqlList.get(0).getName());
		
		return ResultMsg.SUCCESS(exportData);
	}
	
	/**
	 * 从JSON文件导入自定义SQL
	 * @param json
	 * @return
	 * @author xianggang
	 * @date 2019年9月4日 下午12:14:55
	 */
	@Override
	public ResultMsg<String> importJson(String json) {
		
		List<FormCustSql> sqlList = JSONObjectUtil.toList(json, FormCustSql.class);
		
		ResultMsg<String> returnRspMsg = ResultMsg.SUCCESS("导入成功");
		
		for (FormCustSql formCustSql : sqlList) {
			formCustSql.setId(null);
			ResultMsg<String> saveRsp = save(formCustSql);
			// 如果导入失败,则继续导入下一个
			if (!saveRsp.getIsOk()) {
				returnRspMsg = saveRsp;
			}
		}
		
		return returnRspMsg;
	}
	
	@Override
	public void removeCustSqlByIds(String[] aryIds) {
		
		this.removeByIds(aryIds);
		
		for (String id : aryIds) {
			List<FormCustSqlFields> fieldsList = formCustSqlFieldsManager.getListBySqlId(id);
			List<String> fieldsIdList = fieldsList.stream().map(FormCustSqlFields::getId).collect(Collectors.toList());
			formCustSqlFieldsManager.removeByIdList(fieldsIdList);
			formCustSqlRelationMananger.removeByListId(id);
		}
		
	}
	
}
